/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package sv.com.intesal.seguridad.sql;

/**
 *
 * @author ejerez
 */
public class SQLRol {

    public class Roles {

        public static final String insertRoles = ""
                + "insert into roles(codigo_f,codigo_taf,codigo_u,estado_r)"
                + "select f.codigo_f,taf.codigo_taf,u.codigo_u,'I' from formulario as f,tipo_accion_formulario as taf,usuario as u "
                + " where f.codigo_f not in "
                + " ( select r.codigo_f from roles as r where r.codigo_f=f.codigo_f and r.codigo_taf=taf.codigo_taf and r.codigo_u=r.codigo_u) "
                + " order by f.codigo_f,taf.codigo_taf,u.codigo_u";
        public static final String updateRoles = "UPDATE ROLES set estado_r=? where codigo_r=?";
        public static final String updateRoles_x_Modulo = "update roles set estado_r=? where codigo_taf=? and codigo_u=? and codigo_f in ( select f.codigo_f from formulario as f,modulo as m where m.codigo_m=f.codigo_m and m.codigo_m=? )";
        public static final String updateRoles_x_AllModulos = "update roles set estado_r=? where codigo_taf=? and codigo_u=? and codigo_f in ( select f.codigo_f from formulario as f,modulo as m where m.codigo_m=f.codigo_m )";
        public static final String getRoles_x_Usuarios = ""
                + "select m.codigo_m,m.nombre_m,f.codigo_f,f.nombre_f,u.codigo_u "
                + "from roles as r,modulo as m,formulario as f,usuario as u "
                + "where m.codigo_m=f.codigo_m and f.codigo_f=r.codigo_f and u.codigo_u=r.codigo_u and u.codigo_u=? "
                + "group by m.codigo_m,m.nombre_m,f.codigo_f,f.nombre_f,u.codigo_u "
                + "order by m.nombre_m,f.nombre_f";
        public static final String getRol_x_Usuario_x_Formulario = ""
                + "select r.codigo_r,taf.codigo_taf,taf.nombre_taf,r.estado_r "
                + "from roles as r,tipo_accion_formulario as taf,usuario as u "
                + "where taf.codigo_taf=r.codigo_taf and u.codigo_u=r.codigo_u and taf.estado_taf='A' and u.codigo_u=? and r.codigo_f=? "
                + "order by taf.orden_taf";
    }

    public class Perfil {

        public static final String insertPerfilRoles = ""
                + "insert into perfil_roles(codigo_f,codigo_taf,codigo_tp,estado_pr) "
                + "select f.codigo_f,taf.codigo_taf,tp.codigo_tp,'I' from formulario as f,tipo_accion_formulario as taf,tipo_perfil as tp "
                + " where f.codigo_f not in "
                + " ( select pr.codigo_f from perfil_roles as pr where pr.codigo_f=f.codigo_f and pr.codigo_taf=taf.codigo_taf and pr.codigo_tp=tp.codigo_tp) "
                + " order by codigo_f,codigo_taf,codigo_tp";
        public static final String updatePerfilRoles = "UPDATE PERFIL_ROLES set estado_pr=? where codigo_pr=?";
        public static final String updatePerfilRoles_x_Modulo_x_Perfil = "update PERFIL_ROLES set estado_pr=? where codigo_taf=? and codigo_tp=? and codigo_f in ( select f.codigo_f from formulario as f,modulo as m where m.codigo_m=f.codigo_m and m.codigo_m=? )";
        public static final String updatePerfilRoles_x_Modulo_x_AllPerfil = "update PERFIL_ROLES set estado_pr=? where codigo_taf=? and codigo_tp in (select codigo_tp from tipo_perfil where estado_tp='A') and codigo_f in ( select f.codigo_f from formulario as f,modulo as m where m.codigo_m=f.codigo_m and m.codigo_m=? )";
        public static final String updatePerfilRoles_x_AllModulos_x_Perfil = "update PERFIL_ROLES set estado_pr=? where codigo_taf=? and codigo_tp=? and codigo_f in ( select f.codigo_f from formulario as f,modulo as m where m.codigo_m=f.codigo_m )";
        public static final String updatePerfilRoles_x_AllModulos_AllPerfiles = "update PERFIL_ROLES set estado_pr=? where codigo_taf=? and codigo_tp in (select codigo_tp from tipo_perfil where estado_tp='A') and codigo_f in ( select f.codigo_f from formulario as f,modulo as m where m.codigo_m=f.codigo_m )";
        public static final String getPerfilRoles_x_TipoPerfil = ""
                + "select m.codigo_m,m.nombre_m,f.codigo_f,f.nombre_f,tp.codigo_tp,tp.nombre_tp "
                + "from perfil_roles as pr,modulo as m,formulario as f,tipo_perfil as tp "
                + "where m.codigo_m=f.codigo_m and f.codigo_f=pr.codigo_f and tp.codigo_tp=pr.codigo_tp and tp.codigo_tp=? "
                + "group by m.codigo_m,m.nombre_m,f.codigo_f,f.nombre_f,tp.codigo_tp ,tp.nombre_tp "
                + "order by m.nombre_m,f.nombre_f;";
        public static final String getRol_x_TipoPerfil_x_Formulario = ""
                + "select pr.codigo_pr,taf.codigo_taf,taf.nombre_taf,pr.estado_pr "
                + "from perfil_roles as pr,tipo_accion_formulario as taf,tipo_perfil as tp "
                + "where taf.codigo_taf=pr.codigo_taf and tp.codigo_tp=pr.codigo_tp and taf.estado_taf='A' and tp.codigo_tp=? and pr.codigo_f=? "
                + "order by taf.orden_taf";
    }

    public class Usuario {

        public static final String update = "UPDATE ROLES set estado_r=? where codigo_taf=? and codigo_f=? and codigo_u=? ";
    }
}
